import sqlite3
import os

# 数据库文件路径
db_path = r'd:\manage1\graduation-project\manage1\manage1\manage\db.sqlite3'

# 检查数据库文件是否存在
if not os.path.exists(db_path):
    print(f"数据库文件不存在: {db_path}")
    exit()

try:
    # 连接到SQLite数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 检查property_user表是否已存在
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='property_user';")
    if cursor.fetchone():
        print("property_user表已存在，跳过创建")
    else:
        # 创建property_user表
        create_table_sql = """
        CREATE TABLE property_user (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            user_type VARCHAR(20) NOT NULL,
            username VARCHAR(50) NOT NULL UNIQUE,
            password VARCHAR(128) NOT NULL,
            status VARCHAR(20) NOT NULL
        );
        """
        cursor.execute(create_table_sql)
        print("成功创建property_user表")
    
    # 插入示例数据
    try:
        # 插入管理员用户
        cursor.execute(
            "INSERT INTO property_user (user_type, username, password, status) VALUES (?, ?, ?, ?)",
            ('管理员', 'admin', 'admin123', '已激活')
        )
        
        # 插入普通用户
        cursor.execute(
            "INSERT INTO property_user (user_type, username, password, status) VALUES (?, ?, ?, ?)",
            ('普通用户', 'user1', 'user123', '已激活')
        )
        
        # 提交事务
        conn.commit()
        print("成功插入示例数据")
    except sqlite3.IntegrityError:
        print("示例数据已存在，跳过插入")
    
    # 查询表结构和数据
    print("\n表结构:")
    cursor.execute("PRAGMA table_info(property_user)")
    for column in cursor.fetchall():
        print(column)
    
    print("\n表数据:")
    cursor.execute("SELECT * FROM property_user")
    for row in cursor.fetchall():
        print(row)
        
except sqlite3.Error as e:
    print(f"数据库操作错误: {e}")
finally:
    # 关闭数据库连接
    if conn:
        conn.close()
        print("\n数据库连接已关闭")